package com.garen.orm;

import java.lang.reflect.*;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;

import javax.persistence.*;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.RowMapper;

import com.garen.common.AbstractFilter;
import com.garen.common.MsgBean;
import com.garen.common.PageBean;
import com.garen.orm.annotation.Formula;
import com.garen.utils.LangUtils;

/*
 * 实体类映射的表信息
 */
public class OrmTable<T> implements RowMapper<T>{

	//生产SQL语句时,key标志
	public static final String INSERT_KEY = "INSERT#SQL";
	public static final String DELETE_KEY = "DELETE#SQL";
	public static final String UPDATE_KEY = "UPDATE#SQL";
	public static final String SELECT_KEY = "SELECT#SQL";
	public static final String SELECT_PAGE_KEY = "SELECT#PAGE#SQL#";
	public static final String SELECT_ROWMAPPER = "SELECT#ROWMAPPER";
	
	protected static Log log = LogFactory.getLog(OrmTable.class);

	private OrmDBType ormdb;
	
	private String schema;
	
	//表名
	private String tableName;
	
	//实体类型
	private Class<?> entityClass;
	
	//自增类型
	private boolean autoId;
	
	//复合主键
	private boolean cpkey;
	
	//关联的字段信息
	private List<OrmColumn> collist= new ArrayList<OrmColumn>();
	public OrmTable(){
		log.debug("初始化" + this);
	}
	
	public OrmTable(OrmDBType ormdb) {
		this.ormdb = ormdb;
	}
	
	//设置主键自增的值
	public boolean putIdValue(Object obj,Object value){
		log.debug("设置主键" + value);
		if(cpkey) return false;//复合主键不能设置值
		for(OrmColumn ormCol : collist){
			if(ormCol.isId()){
				ormCol.setValue(obj,value);
				break;
			}
		}
		return true;
	}
	
	//判断主键是为为Null,有一个为Null也返回true
	public boolean idIsNull(Object obj){
		boolean isnull = false;
		for(OrmColumn ormCol : collist){
			if(ormCol.isId()){
				if(null == ormCol.getValue(obj)){
					isnull = true;
				}
			}
		}
		return isnull;
	}
	
	//获取主键名称，若多个主键，仅取一个，顺序随机
	public String getIdColName(){
		for(OrmColumn ormCol : collist){
			if(ormCol.isId()){
				return ormCol.getColName();
			}
		}
		return null;
	}
	
	//获取序列名称
	public String getIdSequence(){
		for(OrmColumn ormCol : collist){
			if(ormCol.isId()){
				return ormCol.getSequence();
			}
		}
		return null;
	}
		
	
	/*
	 * 生成insert into SQL语句
	 * nullFlag true 忽略Null字段，false不忽略
	 * null生成错误
	 */
	public Map<String,Object> createSql(Object obj,boolean nullFlag){
		Map<String,Object> paramMap = new HashMap<String,Object>();
		MsgBean mbean = AbstractFilter.getMsgBean();
				
		//生成sql语句
		StringBuilder sb = new StringBuilder();
		if(StringUtils.isNotEmpty(schema)){
			sb.append("insert into " + schema +"."+  tableName);
		}else sb.append("insert into " + tableName);
		String a = null,b = null;
		for(OrmColumn ormCol : collist){
			Object value = ormCol.getValue(obj);
			String colName = ormCol.getColName();
			if(ormCol.isExtCol()) continue;
			if(ormCol.isId()) {//字段为主键
				if(autoId) continue;
				else{
					if(value == null){
						if(ormdb.isOracle()){
							if(a == null){
								a = "(" + colName;
								b = "(" + ormCol.getSequence() +"."+ ormCol.getSequence() + ".nextval ";
							}else{
								a += "," + colName;
								b += "," +  ormCol.getSequence() + ".nextval ";
							}
						}else{
							log.error("非自增表，主键不能为空..");
							mbean.setRetInfo(-1, "非自增表，主键不能为空 !");
							return null;
						}
					}
				}
			}
			//nullFlag true时 忽略null字段
			if(value == null) {
				if(ormCol.isNullable() == false){//不能为空
					mbean.setRetInfo(-2,colName + "字段,"+ ormCol.getFieldName()+"域不能为空 !");
					log.error(mbean.getInfo());
					return null;
				}
				if(nullFlag) continue;
			}
			if(a == null){
				a = "(" + colName;
				b = "(:" + colName;
			}else{
				a += "," + colName;
				b += ",:" + colName;
			}	
			paramMap.put(ormCol.getColName(), value);
		}
		if(a == null || b == null){
			mbean.setRetInfo(-2,"域不能为空 !");
			log.error(mbean.getInfo());
			return null;
		}
		a += ")";
		b += ")";
		sb.append(a);
		sb.append(" values");
		sb.append(b);
		paramMap.put(INSERT_KEY, sb.toString());
		return paramMap;
	}
	
	/*
	 * 更新，根据主键修改
	 * nullFlag true不修改实体中为Null的字段
	 * 返回改变的行数
	 */
	public Map<String,Object> updateSql(Object obj,boolean nullFlag){
		MsgBean mbean = AbstractFilter.getMsgBean();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		//主键列表
		Map<String,Object> keyMap = new HashMap<String,Object>();
		//生成sql语句
		StringBuilder sb = new StringBuilder();
		StringBuilder keysb = new StringBuilder();
		sb.append("update " + tableName + " set ");
		boolean flag = false,keyflag = false;
		for(OrmColumn ormCol : collist){
			Object value = ormCol.getValue(obj);
			String colName = ormCol.getColName();
			//外部字段跳出
			if(ormCol.isExtCol()) continue;
			//若为主键且值为空，为自增类型，则跳过 !
			if(ormCol.isId() == false){
				if(value == null) {
					if(nullFlag) continue;
					if(ormCol.isNullable() == false){//不能为空
						mbean.setRetInfo(-2,colName + "字段,"+ ormCol.getFieldName()+"域不能为空 !");
						log.error(mbean.getInfo());
						return null;
					}
				}
				paramMap.put(colName, value);
				if(flag){
					sb.append(",");
				}else flag = true;
				sb.append(colName + "=:" + colName);
			}else{
				if(value == null) {
					log.debug("更新记录，主键不能为空");
					mbean.setRetInfo(-1, "更新记录，主键不能为空 !");
					return null;//主键为空，返回-1
				}
				else{
					keyMap.put(colName, value);
					if(keyflag){
						keysb.append(" and ");
					}else keyflag = true;
					keysb.append(colName + "=:" + colName);
				}
			}
		}
		sb.append(" where ");
		sb.append(keysb);
		if(flag == false){
			log.debug("没有需要更改的字段");
			return null;//主键为空，返回-1
		}
		for(String k : keyMap.keySet()){
			paramMap.put(k, keyMap.get(k));
		}
		paramMap.put(UPDATE_KEY, sb.toString());
		return paramMap;
	}
	
	/*
	 * 更新，根据主键修改
	 * nullFlag true不修改实体中为Null的字段
	 * 返回改变的行数
	 */
	public Map<String,Object> updateSqlWithField(Object obj,boolean nullFlag,String field){
		MsgBean mbean = AbstractFilter.getMsgBean();
		field +=",";//防止字段名称部分重复 !
		Map<String,Object> paramMap = new HashMap<String,Object>();
		//主键列表
		Map<String,Object> keyMap = new HashMap<String,Object>();
		//生成sql语句
		StringBuilder sb = new StringBuilder();
		StringBuilder keysb = new StringBuilder();
		sb.append("update " + tableName + " set ");
		boolean flag = false,keyflag = false;
		for(OrmColumn ormCol : collist){
			Object value = ormCol.getValue(obj);
			String colName = ormCol.getColName();
			//外部字段跳出
			if(ormCol.isExtCol()) continue;
			//若为主键且值为空，为自增类型，则跳过 !
			if(field.indexOf(colName.toUpperCase() + ",") == -1 
					&& field.indexOf(ormCol.getFieldName().toUpperCase() + ",") == -1 ){//不匹配
				if(value == null && nullFlag) continue;
				paramMap.put(colName, value);
				if(flag){
					sb.append(",");
				}else flag = true;
				sb.append(colName + "=:" + colName);
			}else{//匹配作为条件
				if(value == null) continue;
				keyMap.put(colName, value);
				if(keyflag){
					keysb.append(" and ");
				}else keyflag = true;
				keysb.append(colName + "=:" + colName);
			}
		}
		sb.append(" where ");
		sb.append(keysb);
		if(keyflag == false){
			log.debug("没有需要更改的字段");
			mbean.setRetInfo(1, "更新记录，where条件不能为空 !");
			return null;//主键为空，返回-1
		}
		for(String k : keyMap.keySet()){
			paramMap.put(k, keyMap.get(k));
		}
		paramMap.put(UPDATE_KEY, sb.toString());
		return paramMap;
	}
	
	/*
	 * 实体删除，根据主键ID
	 * 返回改变的行数
	 */
	public Map<String,Object> delSQL(Object obj){
		//主键列表
		Map<String,Object> keyMap = new HashMap<String,Object>();
		//生成sql语句
		StringBuilder sb = new StringBuilder();
		StringBuilder keysb = new StringBuilder();
		sb.append("delete from  " + tableName);
		boolean keyflag = false;
		for(OrmColumn ormCol : collist){
			//外部字段跳出
			if(ormCol.isExtCol()) continue;
			Object value = ormCol.getValue(obj);
			String colName = ormCol.getColName();
			if(value == null) continue;
			//不为Null的字段为条件
			keyMap.put(colName, value);
			if(keyflag){
				keysb.append(" and ");
			}else keyflag = true;
			keysb.append(colName + "=:" + colName);
		}
		if(keyMap.size() == 0){
			return null;
		}
		sb.append(" where ");
		sb.append(keysb);
		keyMap.put(DELETE_KEY, sb.toString());
		return keyMap;
	}
	
	/*
	 * 不分页查询实体列表
	 */
	public Map<String,Object> getListSQL(String addParams,Map<String,Object> addMap,Object obj,String orderBy){
		Map<String,Object> paramMap = new HashMap<String,Object>();
		//生成sql语句
		StringBuilder sb = new StringBuilder();
		createQueryListSQL(paramMap,sb,null,addParams,addMap
				,obj);
		orderBy = addOrderBy(sb,orderBy);//生成排序字段
		paramMap.put(SELECT_KEY, sb.toString());
		return paramMap;
	}
	
	/*
	 * 生成查询SQL语句
	 * paramMap 参数
	 * sb 生成sql语句
	 * pagesb分页查询语句
	 * obj 实体查询
	 * orderBy排序字段
	 * addParams,addMap 附加查询条件
	 */
	private void createQueryListSQL(Map<String,Object> paramMap,StringBuilder sb,
			StringBuilder pagesb,String addParams,Map<String,Object> addMap,Object obj){
		//主键列表
		Map<String,Object> keyMap = new HashMap<String,Object>();
		StringBuilder keysb = new StringBuilder();
		sb.append("select  ");
		//默认分页语句
		if(pagesb == null) pagesb =  new StringBuilder();
		pagesb.append("select  count(1) ");
		boolean flag = false,keyflag = false;
		for(OrmColumn ormCol : collist){
			Object value = ormCol.getValue(obj);
			String colName = ormCol.getColName();
			boolean extCol = ormCol.isExtCol();
			String formulaSQL = ormCol.getFormulaSQL();//伪列
			if(StringUtils.isEmpty(formulaSQL) && extCol) continue;//扩展列，继续
			if(flag) sb.append(",");
			else flag = true;
			if(StringUtils.isEmpty(formulaSQL)){
				sb.append("t." + colName);
			}else{//伪列处理
				if(ormCol.isOnlyOne()){//加入一条显示
					if(ormdb.isMysql()){
						sb.append("( " + formulaSQL + " limit 1) as " + ormCol.getColName());
					}else if(ormdb.isOracle()){
						sb.append("( " + formulaSQL.replace("where", "where rownum = 1 and ") + " ) as " + ormCol.getColName());
					}else if(ormdb.isMssql()){
						sb.append("( " + formulaSQL.replace("select", "select top 1 ") + " ) as " + ormCol.getColName());
					}
				}else{
					sb.append("( " + formulaSQL + " ) as " + ormCol.getColName());
				}
			}
			if(value != null) {
				keyMap.put(colName, value);
				if(keyflag){
					keysb.append(" and ");
				}else keyflag = true;
				keysb.append("t." + colName + "=:" + colName);
			}
		}
		
		sb.append("  from "+ getSchemaValue() + tableName + " t ");
		pagesb.append("  from "+ getSchemaValue() + tableName + " t ");
		
		//加入附加where条件
		if(StringUtils.isNotEmpty(addParams)){
			keyMap.putAll(addMap);
			if(keyflag){
				keysb.append(" and ");
			}
			keysb.append(addParams);
		}
		if(keyMap.size() > 0){
			sb.append(" where " + keysb);
			pagesb.append(" where " + keysb);
			for(String k : keyMap.keySet()){
				paramMap.put(k, keyMap.get(k));
			}
		}
		
	}
	
	//获取默认排序字段
	private String addOrderBy(StringBuilder sb,String orderBy){
		if(StringUtils.isEmpty(orderBy)){//默认排序
			orderBy = getIdColName() + " asc ";
		}
		//加入排序语句
		if(sb != null) sb.append(" order by " + orderBy);
		return orderBy;
	}
	
	//附加参数及查询条件
	public Map<String,Object>  getPageList(PageBean<T> pb,T obj,
			String addParams,Map<String,Object> addMap,String orderBy){
		StringBuilder sb = new StringBuilder();
		Map<String,Object> paramMap = new HashMap<String,Object>();
		StringBuilder pagesb = new StringBuilder();
		createQueryListSQL(paramMap,sb,pagesb,addParams,addMap,obj);
		addPageSql(sb,pb,orderBy);
		paramMap.put(SELECT_KEY, sb.toString());
		paramMap.put(SELECT_PAGE_KEY, pagesb.toString());
		return paramMap;
	}
	
	//sql语句添加mycat前缀
	protected String addPreSQL(String sql){
		return sql;
	}
		
	//不同数据库，分页语句不同对待
	public void addPageSql(StringBuilder sb,PageBean<T> pb,String orderBy){
		orderBy = addOrderBy(sb, orderBy);//获取排序字段
		if(ormdb.isMysql()){
			//加入分页
			sb.append(" limit " + pb.getOffset() +","+ pb.getPageSize());
		}else if(ormdb.isMssql()){
			sb.insert(0,"Select * From (Select ROW_NUMBER() Over (Order By "+ orderBy +") As rowNum, * From ( ");
			sb.append(" ) As TT ) As N Where rowNum > " + pb.getOffset() +" And rowNum <= "+ (pb.getPageSize() + pb.getOffset()) );
			//String pageSql = "Select * From (Select ROW_NUMBER() Over (Order By Floor_ID DESC) As rowNum, * From () As T ) As N Where rowNum > 5 And rowNum <= 10";
		}else if(ormdb.isOracle()){//oracle数据库
			sb.insert(0, "SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( ");
			sb.append(" ) A WHERE ROWNUM <=  " + (pb.getPageSize() + pb.getOffset())  +" ) WHERE RN >= " + pb.getOffset());
		}
	}
	
	/*
	 * 实体删除，根据主键ID
	 * 返回改变的行数
	 */
	public Map<String,Object> delByIdSQL(Integer Id){
		if(cpkey){
			log.debug("复合主键，不接受单个实体");
			return null;//复合主键
		}
		//主键列表
		Map<String,Object> keyMap = new HashMap<String,Object>();
		//生成sql语句
		StringBuilder sb = new StringBuilder();
		StringBuilder keysb = new StringBuilder();
		sb.append("delete from  " + tableName);
		for(OrmColumn ormCol : collist){
			if(ormCol.isId()){
				String colName = ormCol.getColName();
				keyMap.put(colName, Id);
				keysb.append(colName + "=:" + colName);
				break;
			}
		}
		sb.append(" where ");
		sb.append(keysb);
		keyMap.put(DELETE_KEY, sb.toString());
		return keyMap;
	}
	// 初始化
	public boolean init(Class<?> tClass) {
		Table table = (Table) tClass.getAnnotation(Table.class);
		if (table == null) {
			log.debug(tClass.getName() + "实体类Table注解为空");
			return false;
		}
		tableName = table.name();
		schema = table.schema();
		// 初始化字段
		Field[] fields = tClass.getDeclaredFields();
		boolean pk = false;
		for (Field field : fields) {
			try {// 异常继续，故异常在循环内
				OrmColumn ormCol = new OrmColumn();
				Column col = (Column) field.getAnnotation(Column.class);
				// 是否为主键
				Id id = (Id) field.getAnnotation(Id.class);
				//判断是否子查询
				Formula formula = (Formula) field.getAnnotation(Formula.class);
				if(formula != null){//不需要继续遍历别的注解了
					ormCol.setColName(formula.name());
					ormCol.setFormulaSQL(formula.value());
					ormCol.setFieldName(field.getName());
					ormCol.setOnlyOne(formula.onlyOne());
					ormCol.setField(field);
					ormCol.setNullable(false);
					ormCol.setExtCol(true);
					collist.add(ormCol);// 添加
					continue;
				}
				if (col == null)  continue;
				ormCol.setField(field);
				ormCol.setColName(col.name());
				ormCol.setFieldName(field.getName());
				ormCol.setNullable(col.nullable());//是否为空
				ormCol.setExtCol(!col.insertable());
				if (id != null) {
					GeneratedValue gv = (GeneratedValue) field
							.getAnnotation(GeneratedValue.class);
					if(gv != null){
						if (gv.strategy() == GenerationType.IDENTITY)
							autoId = true;// 字段为自增
						else if(gv.strategy() == GenerationType.SEQUENCE){//oracle序列模式
							ormCol.setSequence(gv.generator());//设置模式名称
						}
					}
					
					ormCol.setId(true);// 主键
					if(pk) cpkey = true;//设置为复合主键
					else pk = true;
				}
				collist.add(ormCol);// 添加
			} catch (SecurityException e) {
				e.printStackTrace();
			} catch (IllegalArgumentException e) {
				e.printStackTrace();
			}
		}
		entityClass = tClass;//设置实体类型
		return true;
	}

	//实现接口封装
	@SuppressWarnings("unchecked")
	@Override
	public T mapRow(ResultSet arg0, int arg1) throws SQLException {
		T rowObj = null;
		try {
			Set<String> colset = getResultSetColumns(arg0);
			rowObj = (T) entityClass.newInstance();
			for(OrmColumn ormcol : collist){
				if(contains(colset,ormcol.getColName())){
					Object value = arg0.getObject(ormcol.getColName());
					ormcol.setValue(rowObj, value);
					//log.debug(ormcol.getColName() + "====" + value);
				}
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return rowObj;
	}
	//获取数据集合中的列
	static Set<String> getResultSetColumns(ResultSet rs ) throws SQLException{
		Set<String> colset = new HashSet<>();
		if(rs == null) return colset;
    	ResultSetMetaData meta = rs.getMetaData();
    	int colCount = meta.getColumnCount();
    	//log.debug("开始");
    	for(int i = 1;i <= colCount;i++){//获取所有列
    		colset.add(meta.getColumnLabel(i));
    		//log.debug(i + "," + meta.getColumnLabel(i));
    	}
    	//log.debug("结束");
    	return colset;
	}
	
	//不分大小写遍历set
	private static boolean contains(Set<String> set,String str){
		//log.debug(str);
		boolean ret = false;
		if(str == null) str = "";
		str = str.toLowerCase();
		for(String s : set){
			if(s == null) s = "";
			s = s.toLowerCase();
			if(str.equals(s)){
				ret = true;
				break;
			}
		}
		return ret;
	}
	// 实现接口封装,并过滤字段,包含在fields中的有效
	@SuppressWarnings("unchecked")
	@Deprecated
	public T mapRowFilterIn(ResultSet arg0, int arg1, String fields) throws SQLException {
		T rowObj = null;
		Set<String> fieldset = LangUtils.str2Set(fields);
		try {
			rowObj = (T) entityClass.newInstance();
			String colName = null,fieldName = null;
			for (OrmColumn ormcol : collist) {
				colName = ormcol.getColName().toLowerCase().trim();
				fieldName = ormcol.getFieldName().toLowerCase().trim();
				//两个都不包含，就跳过
				if (fieldset.contains(colName) == false 
						&& fieldset.contains(fieldName) == false)
					continue;
				//赋值
				ormcol.setValue(rowObj, arg0.getObject(colName));
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return rowObj;
	}

	
	// 实现接口封装,并过滤字段,包含在fields中的无效
	@Deprecated
	@SuppressWarnings("unchecked")
	public T mapRowFilterEx(ResultSet arg0, int arg1, String fields) throws SQLException {
		T rowObj = null;
		Set<String> fieldset = LangUtils.str2Set(fields);
		try {
			rowObj = (T) entityClass.newInstance();
			String colName = null,fieldName = null;
			for (OrmColumn ormcol : collist) {
				colName = ormcol.getColName().toLowerCase().trim();
				fieldName = ormcol.getFieldName().toLowerCase().trim();
				//两个有一个包含，就跳过
				if (fieldset.contains(colName) || fieldset.contains(fieldName))
					continue;
				//赋值
				ormcol.setValue(rowObj, arg0.getObject(colName));
			}
		} catch (InstantiationException e) {
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			e.printStackTrace();
		}
		return rowObj;
	}
	
	
	

	public String getSchemaValue() {
		if(StringUtils.isEmpty(schema)) return "";
		return schema + ".";
	}

	public List<OrmColumn> getCollist() {
		return collist;
	}

}
